package com.dao.impl;

import com.bean.*;
import com.dao.BhSyCaseDao;
import com.util.AliPool;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BhSyCaseDaoImpl implements BhSyCaseDao {
    QueryRunner runner = new QueryRunner();
    @Override
    public BhUser bhLogin(String account, int pwd) throws SQLException {
        String sql = "select * from BhUser where uID=? and uName=?";
        System.out.println(sql);
        Connection conn = AliPool.getConn();
        BhUser bu = runner.query(conn,sql,new BeanHandler<BhUser>(BhUser.class),pwd,account);
        conn.close();
        return bu;
    }
    @Test
    public void get(){
        BhSyCaseDao bcd = new BhSyCaseDaoImpl();
        try {
            System.out.println(bcd.bhLogin("樊星",2));;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
    @Override
    public List<BhRole> bhSelRole(int system) throws SQLException {
        List<BhRole> list = null;
        String sql = "select * from BhRole where id=?";
        Connection conn = AliPool.getConn();
        List<BhRole> br = runner.query(conn,sql,new BeanListHandler<>(BhRole.class),system);
        conn.close();
        return br;
    }

    @Override
    public List<UserTopicKu> bhSelUserTK(int uID) throws SQLException {
        List<UserTopicKu> list = null;
        String sql = "select * from usertopicku where uID=?";
        Connection conn = AliPool.getConn();
        list = runner.query(conn,sql,new BeanListHandler<>(UserTopicKu.class),uID);
        conn.close();
        return list;
    }

    @Override
    public void bhInertExcelTopic(List<Bhtopic> list, int uid) throws SQLException {
        Connection conn = AliPool.getConn();
        //查询所有题目的总数
        String sql04 = "select count(*) from bhTopic where uID=?";
        long topIcSum = runner.query(conn,sql04,new ScalarHandler<Long>(),uid);
        int count = Math.toIntExact(topIcSum);
        System.out.println("[count]"+count);
        //查询该用户的所有题目
        String sql = "select * from bhTopic where uID=?";
        List<Bhtopic> btopic = runner.query(conn,sql,new BeanListHandler<>(Bhtopic.class),uid);
        boolean flag = true;
        //计算多少题目合格
        int sumTopic = 0;
        System.out.println("[list]"+list.toString());
        for (Bhtopic btp02:list) {
            for (Bhtopic btp01: btopic) {
                //进行判断,是否有相同题型以及题目的题目
                if (btp02.getcPor().equals(btp01.getcPor())&&btp02.getcType().equals(btp01.getcType())){
                    System.out.printf("1");
                    flag = true;
                    break;
                }else {
                    flag = false;
                }
            }

            System.out.println("[btp02]"+btp02.toString());
            //如果没有相同题目添加
            if (flag==false){
                String sql02 = "insert into bhTopic(uID,cType,cPor,cTrue,cA,cB,cC,cD,cNum)values(?,?,?,?,?,?,?,?,?)";
                int num = runner.update(conn,sql02,
                        uid,
                        btp02.getcType(),
                        btp02.getcPor(),
                        btp02.getcTrue(),
                        btp02.getcA(),
                        btp02.getcB(),
                        btp02.getcC(),
                        btp02.getcD(),
                        btp02.getcNum()
                );

                sumTopic++;
            }else {
                continue;
            }
        }
        if (sumTopic>=1){
            //查询出用户的默认题库
            String sql03 = "select * from usertopicku where uID=? and tName='默认题库'";
            UserTopicKu utk = runner.query(conn,sql03,new BeanHandler<>(UserTopicKu.class),uid);
            //查询出刚刚添加的题目信息
            String sql05 = "select * from bhTopic limit "+count+","+sumTopic;
            List<Bhtopic> btpList = runner.query(conn,sql05,new BeanListHandler<>(Bhtopic.class));
            //将上传成功并且可以添加到数据库的题目添加到'默认题库'中
            String sql06 = "insert into userkutopic values(?,?)";
            for (Bhtopic bt:btpList) {
                int num = runner.update(conn,sql06,utk.getUtID(),bt.getTopIcID());
            }
        }
        conn.close();
    }

    @Override
    public List<Bhtopic> selBhTopicForProAType(List<Bhtopic> list) throws SQLException {
        String sql = "select * from BhTopIc where cPor=? and cType=?";
        Connection conn = AliPool.getConn();
        List<Bhtopic> listBt = new ArrayList<>();
        Bhtopic btp = null;
        for (Bhtopic bt:list) {
            btp = runner.query(conn,sql,new BeanHandler<>(Bhtopic.class),bt.getcPor(),bt.getcType());
            listBt.add(btp);
        }
        conn.close();
        return listBt;
    }

    @Override
    public void addBhTopIcTm(Bhcasetm btm) throws SQLException {
        String sql = "insert into BhCaseTm values(?,?,?)";
        Connection conn = AliPool.getConn();
        runner.update(conn,sql,btm.getcUrl(),btm.getcNum(),btm.getcID());
        conn.close();
    }

    @Override
    public void addBhCase(Bhcase bhcase) throws SQLException {
        String sql = "insert into BhCase values(?,?,?,?,?,?)";
        Connection conn = AliPool.getConn();
        runner.update(conn,sql,bhcase.getuID(),bhcase.getcUrl(),bhcase.getcName(),bhcase.getcFlag(),bhcase.getcBegin(),bhcase.getcEnd());
        conn.close();
    }

    @Override
    public List<Bhtopic> getBhTopicForPor(int uId, String[] tPor) throws SQLException {
        List<Bhtopic> list = new ArrayList<>();
        String sql = "select * from Bhtopic where uID=? and cPor=?";
        Connection conn = AliPool.getConn();
        for (int i = 0; i < tPor.length ; i++) {
            Bhtopic bt = runner.query(conn,sql,new BeanHandler<>(Bhtopic.class),uId,tPor[i]);
            System.out.println("--------------------------------");
            System.out.println(bt);
            System.out.println("用户ID:"+uId+"----题目:"+tPor[i]);
            list.add(bt);
        }
        return list;
    }
}
